
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/19/2011 10:47:52
-- Generated from EDMX file: D:\Test Projects\New\ATS 2.1\ATS.Data\ATSModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ats];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_DisposedAssets_MainAsset]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DisposedAssets] DROP CONSTRAINT [FK_DisposedAssets_MainAsset];
GO
IF OBJECT_ID(N'[dbo].[FK_Images_MainAsset]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Images] DROP CONSTRAINT [FK_Images_MainAsset];
GO
IF OBJECT_ID(N'[dbo].[FK_LeasedAsset_MainAssetTable]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[LeasedAsset] DROP CONSTRAINT [FK_LeasedAsset_MainAssetTable];
GO
IF OBJECT_ID(N'[dbo].[FK_MainAssetsTable_Category]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MainAssetsTable] DROP CONSTRAINT [FK_MainAssetsTable_Category];
GO
IF OBJECT_ID(N'[dbo].[FK_MainAssetsTable_Departments]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MainAssetsTable] DROP CONSTRAINT [FK_MainAssetsTable_Departments];
GO
IF OBJECT_ID(N'[dbo].[FK_MainAssetsTable_Location]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MainAssetsTable] DROP CONSTRAINT [FK_MainAssetsTable_Location];
GO
IF OBJECT_ID(N'[dbo].[FK_MainAssetsTable_Persons]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MainAssetsTable] DROP CONSTRAINT [FK_MainAssetsTable_Persons];
GO
IF OBJECT_ID(N'[dbo].[FK_Persons_Department]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Persons] DROP CONSTRAINT [FK_Persons_Department];
GO
IF OBJECT_ID(N'[dbo].[FK_Warranty_MainAssetTable]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Warranty] DROP CONSTRAINT [FK_Warranty_MainAssetTable];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[admPasswordConfig]', 'U') IS NOT NULL
    DROP TABLE [dbo].[admPasswordConfig];
GO
IF OBJECT_ID(N'[dbo].[App_info]', 'U') IS NOT NULL
    DROP TABLE [dbo].[App_info];
GO
IF OBJECT_ID(N'[dbo].[Category]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Category];
GO
IF OBJECT_ID(N'[dbo].[CompanyDetails]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CompanyDetails];
GO
IF OBJECT_ID(N'[dbo].[Departments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Departments];
GO
IF OBJECT_ID(N'[dbo].[DisposedAssets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DisposedAssets];
GO
IF OBJECT_ID(N'[dbo].[Images]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Images];
GO
IF OBJECT_ID(N'[dbo].[LeasedAsset]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LeasedAsset];
GO
IF OBJECT_ID(N'[dbo].[Locations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Locations];
GO
IF OBJECT_ID(N'[dbo].[MainAssetsTable]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MainAssetsTable];
GO
IF OBJECT_ID(N'[dbo].[Persons]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Persons];
GO
IF OBJECT_ID(N'[dbo].[RFIDScanHistory]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RFIDScanHistory];
GO
IF OBJECT_ID(N'[dbo].[UserTable]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserTable];
GO
IF OBJECT_ID(N'[dbo].[Warranty]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Warranty];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'MainAssetsTables'
CREATE TABLE [dbo].[MainAssetsTables] (
    [RFIDno] nvarchar(50)  NULL,
    [Assetno] nvarchar(50)  NOT NULL,
    [DateAcquired] datetime  NULL,
    [AssetAmount] decimal(18,0)  NULL,
    [CategoryCode] nvarchar(50)  NULL,
    [LocationCode] nvarchar(50)  NULL,
    [DeptCode] nvarchar(50)  NULL,
    [Section] nvarchar(50)  NULL,
    [AssignToEmployeeCode] nvarchar(50)  NULL,
    [DescriptiveName] nvarchar(50)  NULL,
    [Createdby] nvarchar(50)  NULL,
    [DateCreated] datetime  NULL,
    [LastModified] datetime  NULL,
    [LastModifiedBy] nvarchar(50)  NULL,
    [EstimatedValue] decimal(18,0)  NULL,
    [ReplacementCost] decimal(18,0)  NULL,
    [Condition] nvarchar(50)  NULL,
    [Status] nvarchar(50)  NULL,
    [SerialNumber] nvarchar(50)  NULL,
    [InvoiceNumber] nvarchar(50)  NULL,
    [BoughtFrom] nvarchar(50)  NULL,
    [BaseCost] decimal(18,0)  NULL,
    [VAT] decimal(18,0)  NULL,
    [Manufacturer] nvarchar(50)  NULL,
    [BrandName] nvarchar(50)  NULL,
    [ModelNumber] nvarchar(50)  NULL,
    [Remarks] nvarchar(max)  NULL,
    [SalvageValue] decimal(18,0)  NULL,
    [LastScanDate] datetime  NULL
);
GO

-- Creating table 'admPasswordConfigs'
CREATE TABLE [dbo].[admPasswordConfigs] (
    [PWLength] int  NULL,
    [PWExpiration] int  NULL,
    [PWFormat1] bit  NULL,
    [PWFormat2] bit  NULL,
    [PWFormat3] bit  NULL,
    [PWLockAccount] int  NULL,
    [PWDifferentFromID] bit  NULL,
    [id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'UserTables'
CREATE TABLE [dbo].[UserTables] (
    [Unique_Key] int IDENTITY(1,1) NOT NULL,
    [Username] nvarchar(50)  NULL,
    [Hashed] nvarchar(1500)  NULL,
    [Salt] nvarchar(150)  NULL,
    [LastPasswordChangeDateTime] datetime  NULL,
    [AccountLocked] bit  NULL,
    [LockedDateTime] datetime  NULL,
    [NumberOfPasswordTry] int  NULL,
    [LastLogin] datetime  NULL,
    [ChangePassword] bit  NULL
);
GO

-- Creating table 'CompanyDetails'
CREATE TABLE [dbo].[CompanyDetails] (
    [CompanyName] nvarchar(2500)  NULL,
    [CompanyID] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'Categories'
CREATE TABLE [dbo].[Categories] (
    [CategoryId] int IDENTITY(1,1) NOT NULL,
    [CategoryName] nvarchar(50)  NULL,
    [CategoryCode] nvarchar(50)  NOT NULL,
    [CategoryDesc] nvarchar(1500)  NULL,
    [DepreciationMethod] int  NULL,
    [Years] int  NULL,
    [AssetClass] nvarchar(50)  NULL,
    [IsActive] bit  NULL
);
GO

-- Creating table 'RFIDScanHistories'
CREATE TABLE [dbo].[RFIDScanHistories] (
    [RFIDNum] nvarchar(50)  NOT NULL,
    [DateScanned] datetime  NOT NULL
);
GO

-- Creating table 'App_info'
CREATE TABLE [dbo].[App_info] (
    [property] nvarchar(50)  NULL,
    [value] nvarchar(50)  NULL,
    [id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'Departments'
CREATE TABLE [dbo].[Departments] (
    [DepartmentId] int IDENTITY(1,1) NOT NULL,
    [DepartmentName] nvarchar(50)  NOT NULL,
    [DepartmentCode] nvarchar(50)  NOT NULL,
    [DepartmentDesc] nvarchar(1500)  NULL
);
GO

-- Creating table 'DisposedAssets'
CREATE TABLE [dbo].[DisposedAssets] (
    [Assetno] nvarchar(50)  NOT NULL,
    [DateSold] datetime  NULL,
    [AmountSold] decimal(18,0)  NULL,
    [Profit] decimal(18,0)  NULL,
    [DisposalGainLoss] decimal(18,0)  NULL,
    [DisposalAccmDepr] decimal(18,0)  NULL,
    [DisposalBV] decimal(18,0)  NULL,
    [DisposalMethod] nvarchar(50)  NULL
);
GO

-- Creating table 'Images'
CREATE TABLE [dbo].[Images] (
    [AssetNo] nvarchar(50)  NULL,
    [ImageName] nvarchar(50)  NULL,
    [ImageLocation] nvarchar(1500)  NULL,
    [ImageCtr] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'LeasedAssets'
CREATE TABLE [dbo].[LeasedAssets] (
    [AssetNo] nvarchar(50)  NOT NULL,
    [LeaseBegin] datetime  NULL,
    [LeaseEnd] datetime  NULL,
    [LeaseDescription] nvarchar(1500)  NULL
);
GO

-- Creating table 'Locations'
CREATE TABLE [dbo].[Locations] (
    [LocationId] int IDENTITY(1,1) NOT NULL,
    [LocationName] nvarchar(50)  NULL,
    [LocationCode] nvarchar(50)  NOT NULL,
    [LocationDesc] nvarchar(1500)  NULL,
    [IsActive] bit  NULL
);
GO

-- Creating table 'Persons'
CREATE TABLE [dbo].[Persons] (
    [id] int IDENTITY(1,1) NOT NULL,
    [EmployeeID] nvarchar(50)  NOT NULL,
    [Title] int  NOT NULL,
    [Designation] nvarchar(50)  NULL,
    [FirstName] nvarchar(50)  NULL,
    [LastName] nvarchar(50)  NULL,
    [MiddleInitial] nvarchar(50)  NULL,
    [Gender] int  NULL,
    [DateOfBirth] datetime  NULL,
    [Department] nvarchar(50)  NULL,
    [AdditionalInformation] nvarchar(2000)  NULL,
    [ExtensionNumber] nvarchar(50)  NULL,
    [EmailAddress] nvarchar(50)  NULL
);
GO

-- Creating table 'Warranties'
CREATE TABLE [dbo].[Warranties] (
    [AssetNo] nvarchar(50)  NOT NULL,
    [WarrantyType] nvarchar(50)  NULL,
    [WarrantyExpiration] datetime  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Assetno] in table 'MainAssetsTables'
ALTER TABLE [dbo].[MainAssetsTables]
ADD CONSTRAINT [PK_MainAssetsTables]
    PRIMARY KEY CLUSTERED ([Assetno] ASC);
GO

-- Creating primary key on [id] in table 'admPasswordConfigs'
ALTER TABLE [dbo].[admPasswordConfigs]
ADD CONSTRAINT [PK_admPasswordConfigs]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [Unique_Key] in table 'UserTables'
ALTER TABLE [dbo].[UserTables]
ADD CONSTRAINT [PK_UserTables]
    PRIMARY KEY CLUSTERED ([Unique_Key] ASC);
GO

-- Creating primary key on [CompanyID] in table 'CompanyDetails'
ALTER TABLE [dbo].[CompanyDetails]
ADD CONSTRAINT [PK_CompanyDetails]
    PRIMARY KEY CLUSTERED ([CompanyID] ASC);
GO

-- Creating primary key on [CategoryCode] in table 'Categories'
ALTER TABLE [dbo].[Categories]
ADD CONSTRAINT [PK_Categories]
    PRIMARY KEY CLUSTERED ([CategoryCode] ASC);
GO

-- Creating primary key on [RFIDNum], [DateScanned] in table 'RFIDScanHistories'
ALTER TABLE [dbo].[RFIDScanHistories]
ADD CONSTRAINT [PK_RFIDScanHistories]
    PRIMARY KEY CLUSTERED ([RFIDNum], [DateScanned] ASC);
GO

-- Creating primary key on [id] in table 'App_info'
ALTER TABLE [dbo].[App_info]
ADD CONSTRAINT [PK_App_info]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [DepartmentCode] in table 'Departments'
ALTER TABLE [dbo].[Departments]
ADD CONSTRAINT [PK_Departments]
    PRIMARY KEY CLUSTERED ([DepartmentCode] ASC);
GO

-- Creating primary key on [Assetno] in table 'DisposedAssets'
ALTER TABLE [dbo].[DisposedAssets]
ADD CONSTRAINT [PK_DisposedAssets]
    PRIMARY KEY CLUSTERED ([Assetno] ASC);
GO

-- Creating primary key on [ImageCtr] in table 'Images'
ALTER TABLE [dbo].[Images]
ADD CONSTRAINT [PK_Images]
    PRIMARY KEY CLUSTERED ([ImageCtr] ASC);
GO

-- Creating primary key on [AssetNo] in table 'LeasedAssets'
ALTER TABLE [dbo].[LeasedAssets]
ADD CONSTRAINT [PK_LeasedAssets]
    PRIMARY KEY CLUSTERED ([AssetNo] ASC);
GO

-- Creating primary key on [LocationCode] in table 'Locations'
ALTER TABLE [dbo].[Locations]
ADD CONSTRAINT [PK_Locations]
    PRIMARY KEY CLUSTERED ([LocationCode] ASC);
GO

-- Creating primary key on [EmployeeID] in table 'Persons'
ALTER TABLE [dbo].[Persons]
ADD CONSTRAINT [PK_Persons]
    PRIMARY KEY CLUSTERED ([EmployeeID] ASC);
GO

-- Creating primary key on [AssetNo] in table 'Warranties'
ALTER TABLE [dbo].[Warranties]
ADD CONSTRAINT [PK_Warranties]
    PRIMARY KEY CLUSTERED ([AssetNo] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CategoryCode] in table 'MainAssetsTables'
ALTER TABLE [dbo].[MainAssetsTables]
ADD CONSTRAINT [FK_MainAssetsTable_Category]
    FOREIGN KEY ([CategoryCode])
    REFERENCES [dbo].[Categories]
        ([CategoryCode])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MainAssetsTable_Category'
CREATE INDEX [IX_FK_MainAssetsTable_Category]
ON [dbo].[MainAssetsTables]
    ([CategoryCode]);
GO

-- Creating foreign key on [DeptCode] in table 'MainAssetsTables'
ALTER TABLE [dbo].[MainAssetsTables]
ADD CONSTRAINT [FK_MainAssetsTable_Departments]
    FOREIGN KEY ([DeptCode])
    REFERENCES [dbo].[Departments]
        ([DepartmentCode])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MainAssetsTable_Departments'
CREATE INDEX [IX_FK_MainAssetsTable_Departments]
ON [dbo].[MainAssetsTables]
    ([DeptCode]);
GO

-- Creating foreign key on [Department] in table 'Persons'
ALTER TABLE [dbo].[Persons]
ADD CONSTRAINT [FK_Persons_Department]
    FOREIGN KEY ([Department])
    REFERENCES [dbo].[Departments]
        ([DepartmentCode])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Persons_Department'
CREATE INDEX [IX_FK_Persons_Department]
ON [dbo].[Persons]
    ([Department]);
GO

-- Creating foreign key on [Assetno] in table 'DisposedAssets'
ALTER TABLE [dbo].[DisposedAssets]
ADD CONSTRAINT [FK_DisposedAssets_MainAsset]
    FOREIGN KEY ([Assetno])
    REFERENCES [dbo].[MainAssetsTables]
        ([Assetno])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [AssetNo] in table 'Images'
ALTER TABLE [dbo].[Images]
ADD CONSTRAINT [FK_Images_MainAsset]
    FOREIGN KEY ([AssetNo])
    REFERENCES [dbo].[MainAssetsTables]
        ([Assetno])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Images_MainAsset'
CREATE INDEX [IX_FK_Images_MainAsset]
ON [dbo].[Images]
    ([AssetNo]);
GO

-- Creating foreign key on [AssetNo] in table 'LeasedAssets'
ALTER TABLE [dbo].[LeasedAssets]
ADD CONSTRAINT [FK_LeasedAsset_MainAssetTable]
    FOREIGN KEY ([AssetNo])
    REFERENCES [dbo].[MainAssetsTables]
        ([Assetno])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [LocationCode] in table 'MainAssetsTables'
ALTER TABLE [dbo].[MainAssetsTables]
ADD CONSTRAINT [FK_MainAssetsTable_Location]
    FOREIGN KEY ([LocationCode])
    REFERENCES [dbo].[Locations]
        ([LocationCode])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MainAssetsTable_Location'
CREATE INDEX [IX_FK_MainAssetsTable_Location]
ON [dbo].[MainAssetsTables]
    ([LocationCode]);
GO

-- Creating foreign key on [AssignToEmployeeCode] in table 'MainAssetsTables'
ALTER TABLE [dbo].[MainAssetsTables]
ADD CONSTRAINT [FK_MainAssetsTable_Persons]
    FOREIGN KEY ([AssignToEmployeeCode])
    REFERENCES [dbo].[Persons]
        ([EmployeeID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MainAssetsTable_Persons'
CREATE INDEX [IX_FK_MainAssetsTable_Persons]
ON [dbo].[MainAssetsTables]
    ([AssignToEmployeeCode]);
GO

-- Creating foreign key on [AssetNo] in table 'Warranties'
ALTER TABLE [dbo].[Warranties]
ADD CONSTRAINT [FK_Warranty_MainAssetTable]
    FOREIGN KEY ([AssetNo])
    REFERENCES [dbo].[MainAssetsTables]
        ([Assetno])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------